package com.wy.util;

/**
 * Created by wy on 2017/11/22.
 */
        import java.io.File;
        import java.io.FileWriter;
        import java.io.IOException;
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.PreparedStatement;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.text.ParseException;
        import java.text.SimpleDateFormat;
        import java.util.ArrayList;
        import java.util.Calendar;
        import java.util.Date;
        import java.util.HashMap;
        import java.util.List;
        import java.util.Map;
        import java.util.regex.Matcher;
        import java.util.regex.Pattern;

        import org.apache.commons.lang3.StringUtils;

      //  import com.campus.common.model.SchoolTime;
        import com.wy.campus.model.Room;

public class gxtcRoomServiceImpl1 {
    public static void main(String[] args) {
        gxtcRoomServiceImpl1 cs = new gxtcRoomServiceImpl1();
        cs.testOracle();
        // cs.getCxjc("第9,10节");
    }


    //t_jsjbsjlb
	/*private static String js = "SELECT(CASE WHEN func_split (jxlm, ';', 1) = '' THEN func_split (jxlm, ';', 2) WHEN func_split (jxlm, ';', 1) != '' THEN func_split (jxlm, ';', 1) END) AS jxlm FROM	t_coursebksxkjs"
			+ " WHERE jxlm != ';' AND jxlm != ';;;' AND jxlm != ';;;;' AND jxlm != '' AND zxjxjhh='2016-2017-2-2' GROUP BY jxlm";divisionid--buildingid--usedate
*/
    private static String js_insert = "INSERT INTO `mobilecampus`.`t_room` (`roomtype`,`roomname`,`divisionname`,`hasseat`, `examseat`,`buildingname`, `xn`,`xq`,`usedate`,`schoolid`,`divisionid`,`buildingid`)"
            + " VALUES" + "(" + "'{roomtype}'," +  "'{roomname}'," + "'{divisionname}',"+ "'{hasseat}',"  + "'{examseat}'," + "'{buildingname}',"  + "'{xn}'," + "'{xq}'," +"'{usedate}'," +"'178'," + "'{divisionid}'," +"'{buildingid}'" +");";

    //校历
    private static String xl = "SELECT * FROM CHG_BJGX.T_SCHOOLTIME  WHERE XN='2017-2018' and XQ=1 and TYPE=1 and SCHOOLID=178";
    private static String xl2 = "SELECT * FROM mobilecampus.t_schooltime  WHERE XN='2017-2018' and XQ=1 and TYPE=1 and schoolid=178";//从本地数据库查询
    //private static String xl ="SELECT * FROM CHG_BJGX.XLRQ";

    // 这一个表
    String js1 = "SELECT l.XQMC,b.JXLMC,j.JSH,j.JXLH,j.YXZWS,j.KSZWS,j.JSYT FROM GXJX_JSJBSJZLB j,GXXX_XQJBSJZL l,JXLH_WZH b WHERE j.xqh = l.xqh AND j.jxlh = b.JXLH";

    private static String initWeek = "000000000000000000000000";

    /**
     * 一个非常标准的连接Oracle数据库的示例代码
     */
    public void testOracle() {
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        PreparedStatement pst = null;
        FileWriter fw = null;
        List<Room> rooms = new ArrayList<>();
        try {
            File file=new File("gxtc广西师范大学空闲教室.sql");
            //System.out.println(file.getAbsolutePath());
            fw = new FileWriter(file);
            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            //url：jdbc:oracle:thin:@172.16.12.209:1521:CHG_BJGX  CHG_BJGX
            //jdbc:oralce:thin:10.1.112.110:1521:heal      jdbc:oracle:thin:@127.0.0.1:1523:urpdb2

//            String url = "jdbc:oracle:thin:@172.16.12.209:1521:ORCL";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
//            String user = "CHG_BJGX";// 用户名,系统默认的账户名
//            String password = "CHG_BJGX";// 你安装时选设置的密码
            String url = "jdbc:oracle:thin:@10.203.2.176:1521:CSSIT";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
            String user = "zju_app";// 用户名,系统默认的账户名
            String password = "app456";// 你安装时选设置的密码

            con = DriverManager.getConnection(url, user, password);// 获取连接
            System.out.println("连接成功！");
            String sql = js1;// 预编译语句，“？”代表参数
            //String sql1 = js1;// 预编译语句，“？”代表参数（教室详细）
            //pre = con.prepareStatement(sql1);// 实例化预编译语句
            //result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            //PreparedStatement prexl = con.prepareStatement(xl);
            //ResultSet resultxl = prexl.executeQuery();
            //Map<String, List<Date>> map = getSchoolTime(xl, con, pst);
            Map<String, List<Date>> map = getSchoolTime222();
            Map<String, Map<String, String>>  mapseat = new  HashMap<>();
            //seat(result, mapseat);
            addRooms(map, sql, con, pst, rooms, mapseat);

            StringBuilder temp = new StringBuilder();
            for (Room room : rooms) {
                List<String> newStringList=new ArrayList<>();
                if(null!=room.getRoomname()){
                    newStringList.add(room.getRoomname());
                }
                if(null!=room.getRoomtype()){
                    newStringList.add(room.getRoomname());
                }
                //roomtype`,`roomname`,`divisionname`,`hasseat`, `examseat`,`buildingname`, `xn`,`xq`,`schoolid
                temp.append(js_insert.replaceAll("\\{roomname\\}", room.getRoomname())//教室名称
                        .replaceAll("\\{roomtype\\}",room.getRoomtype()+"")//教室类型
                        .replaceAll("\\{buildingname\\}",room.getBuildingname())//建筑名称
                        .replaceAll("\\{hasseat\\}",room.getHasseat()+"")
                        .replaceAll("\\{examseat\\}",room.getExamseat()+"")
                        .replaceAll("\\{xn\\}",room.getXn())
                        .replaceAll("\\{xq\\}",room.getXq())
                        .replaceAll("\\{usedate\\}", room.getUsedate())
                        .replaceAll("\\{divisionname\\}", room.getDivisionname())
                        .replaceAll("\\{divisionid\\}", room.getDivisionid()+"")
                        .replaceAll("\\{buildingid\\}", room.getBuildingid()+""))
                        .append("\n");
            }
            fw.append(temp);
            // 当结果集不为空时
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭！");
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                fw.flush();
                fw.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    private void addRooms(Map<String, List<Date>> map, String sql, Connection conn, PreparedStatement pst,
                          List<Room> rooms,  Map<String, Map<String, String>> mapseat) throws SQLException {
        int j=0 ;
        //按周次遍历
        for (String zc : map.keySet()) {

            zc = zc.replace("zc_", "");
            // System.out.println(zc + "," + map.get(zc));

            pst = conn.prepareStatement(sql);

            // Date date = dates.get(i - 1);
            List<Date> dates = map.get("zc_" + zc);
            int K=0 ;
            for (int i = 1; i <= 7; i++) {//一周所有时间
                // 一星期
                //pst.setInt(1, i);
                //pst.setInt(2, Integer.parseInt(zc));
                Date date = dates.get(i - 1);
                // System.out.println(dateUtil.format(date) + "-" + "星期[" +
                // i + "]");
                //for (Map<String, String> mapse : mapseat) {//教室
                ResultSet rs = pst.executeQuery();

                K++;
                j++;
                int l=0;
                while (rs.next()) {
                    l++;
                    String jxxq = rs.getString("XQMC");//校区名称
                    String xqid="";//校区类别
                    if(jxxq.equals("长岗校区")){xqid="2";}
                    if(jxxq.equals("五合校区")){xqid="4";}
                    if(jxxq.equals("明秀校区")){xqid="1";}
                    if(jxxq.equals("明阳校区")){xqid="3";}
                    String jxlh = rs.getString("JXLH");//教学楼号
                    Room room = new Room();
                    room.setSchoolid(178);
                    room.setRoomname(rs.getString("JSH"));//教室名
                    room.setRoomtype(rs.getString("JSYT"));//教室类型
                    room.setXn("2017-2018");
                    room.setXq("1");
                    room.setDivisionname(rs.getString("XQMC"));//校区名
                    room.setDivisionid(Integer.parseInt(xqid));
                    room.setBuildingname(rs.getString("JXLMC"));//教学楼名
                    room.setBuildingid(Integer.parseInt(jxlh));
                    room.setHasseat(null==rs.getString("YXZWS")?null:Integer.parseInt(rs.getString("YXZWS")));//上课座位数
                    room.setExamseat(null==rs.getString("KSZWS")?null:Integer.parseInt(rs.getString("KSZWS")));//考试座位数
                    String strFormat = "yyyy-MM-dd";
                    SimpleDateFormat dateFormat = new SimpleDateFormat(strFormat);
                    if (date != null){
                        room.setUsedate(dateFormat.format(date));
                    }

                    rooms.add(room);

                }
                System.out.println(l+".....................");
                //}

            }
            System.out.println(K+"----------------");
        }
        System.out.println(j+"++++++++++++++++++++++");
    }


    private void seat(ResultSet rs1, Map<String, Map<String, String>> mapseat) throws SQLException {
        while (rs1.next()) {
            // xqm,jxlm,jsmc,jslxdm,skzws,kszws
            // 学区代码，教学楼明，教室名称，教室类型代码，上课座位，考试座位
            String xqm = rs1.getString("xqdm");
            String jxlm = rs1.getString("jxlmc");
            String jsmc = rs1.getString("jsmc");

            // String jslxdm = rs1.getString("jslxdm");

            //int skzws = rs1.getInt("skzws");
            //int kszws = rs1.getInt("kszws");

            String key = jsmc;
            // System.out.println(key);
            Map<String, String> maps = new HashMap<String, String>();
            maps.put("xqm", xqm);
            maps.put("jxlm", jxlm);
            maps.put("jsmc", jsmc);
            mapseat.put(key, maps);
        }
    }

    public Map<String, List<Date>> getSchoolTime(String sql, Connection conn, PreparedStatement pst) {
        //List<SchoolTime> list = schoolTimeRedisDAO.getSchoolTime("2016-2017", 2, 151);
        Map<String, List<Date>> map = new HashMap<>();
        try{
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();


            while (rs.next()) {
                int type = rs.getInt("type");
                int xlzc = rs.getInt("zc");
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String starttime = rs.getString("starttime");
                Date startDate=formatter.parse(starttime);
                if (type == 1) {
                    String zc = "zc_" +xlzc;
                    List<Date> date = new ArrayList<>();

                    Calendar calendar = Calendar.getInstance();
                    // System.out.println(zc);
                    for (int i = 0; i < 7; i++) {
                        calendar.setTime(startDate);
                        calendar.add(Calendar.DAY_OF_MONTH, i);
                        date.add(calendar.getTime());
                        // System.out.println(calendar.getTime());
                    }
                    map.put(zc, date);
                }

            }

        }catch(Exception e){
            System.out.println(e);
            System.out.println("date is error");
        }
        return map;
    }







    private int getCxjc(String str) {
        Pattern pattern = Pattern.compile("第(.*?)节");
        Matcher mathcer = pattern.matcher(str);
        String s = "";
        while (mathcer.find()) {
            s = mathcer.group(1);
        }
        if (s.contains(",")) {
            return s.split(",").length;
        }
        return 1;
    }

    private String jashDelDul(String str) {

        StringBuilder sb = new StringBuilder();

        String[] strArr = str.split(";");
        for (int i = 0; i < strArr.length; i++) {
            if (i != 0) {
                if (strArr[i] != strArr[i - 1]) {
                    sb.append(";").append(strArr[i]);
                }
            } else {
                sb.append(strArr[i]);
            }
        }
        return sb.toString();
    }

    private int getSkjc(String str) {
        Pattern pattern = Pattern.compile("第(.*?)节");
        Matcher mathcer = pattern.matcher(str);
        String s = "";
        while (mathcer.find()) {
            s = mathcer.group(1);
        }
        if (s.contains(",")) {
            String[] sArr = s.split(",");
            return Integer.parseInt(sArr[0]);
        }
        return Integer.parseInt(s);
    }

    private String getSkzc(StringBuilder sb, int start, int end) {
        for (; start < end; start++) {
            sb.replace(start - 1, start, "1");
        }
        // System.out.println(sb);

        return sb.toString();
    }
    private String getHour(String string) {
        String i="1";
        try{
            String[] arr=string.split("-");
            i=String.valueOf(Integer.valueOf(arr[0].split(":")[0])-8+1);
        }catch (Exception e) {

        }
        return i;
    }

    private String getSection(String string) {
        if (StringUtils.isEmpty(string)) {
            return "0";
        } else {
            try {
                String[] arr = string.split("-");
                string = String.valueOf(Integer.valueOf(arr[1].split(":")[0])
                        - Integer.valueOf(arr[0].split(":")[0]) + 1);
            } catch (Exception e) {
                string = "0";
            }
            return string;
        }
    }




    public  Connection getConnectionMysql(){
        Connection conn = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://172.16.12.193:3306/mobilecampus";
        String user = "root";
        String pwd = "gxtc2016";
        try {
            Class.forName(driver);
            try {
                conn = DriverManager.getConnection(url, user, pwd);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //获得校历时间 Map,其中key为上课周次，value为该周次所对应的一周的日期
    private Map<String, List<Date>> getSchoolTime222(){
        String sql = "SELECT * FROM t_schooltime WHERE xn='2017-2018' and xq=1 and type=1 and schoolid=178";
        Connection conn = getConnectionMysql();
         ResultSet rs = null;
         PreparedStatement ps = null;
        Map<String, List<Date>> map = new HashMap<String, List<Date>>();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int type = rs.getInt("type");
                int xlzc = rs.getInt("zc");
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String starttime = rs.getString("starttime");
                Date startDate= null;
                try {
                    startDate =  formatter.parse(starttime);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if (type == 1) {
                    /**周次zc_1,第1周所对应的时间*/
                    String zc = "zc_" +xlzc;
                    List<Date> date = new ArrayList<Date>();
                    Calendar calendar = Calendar.getInstance();
                    // System.out.println(zc);
                    for (int i = 0; i < 7; i++) {
                        calendar.setTime(startDate);
                        calendar.add(Calendar.DAY_OF_MONTH, i);
                        date.add(calendar.getTime());
                        //System.out.println(calendar.getTime());
                    }
                    map.put(zc, date);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
                ps.close();
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return  map;
    }



}
